
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;

/*
 * This class allows to upgrade the provide mySQL databases with the structural changes
 * introduced by our expansion of the system
 */

/**
 *
 * @author luisrocha
 */
public class UpgradeEEPOrderDB
{
    
        /**
    * @param args the command line arguments
    */
    public static void main(String args[]) {
        UpgradeEEPOrderDB.Upgrade();
       
    }
    
    
    public static void Upgrade()
    {
        
      //initialize connection
      Connection OrderDB = MySqlDBConnection.Connect(
                "localhost", "orderinfo");
      
      //initialize SQL statement object
      Statement mySQLstmt = null;
      ResultSet res = null;
      
      try {
            mySQLstmt = OrderDB.createStatement();
      } catch (SQLException ex) {
            Logger.getLogger(UpgradeEEPOrderDB.class.getName()).log(Level.SEVERE, null, ex);
      }
      
      System.out.println("Starting Upgrade of Order table...");
      
      //add user id field to table to track who created an order
      try {
          //check first if field userid already exists
          String query1 = "SELECT * FROM information_schema.COLUMNS "
              + "WHERE TABLE_SCHEMA = 'orderinfo' AND TABLE_NAME = 'orders' "
              + "AND COLUMN_NAME = 'userid'";
          res = null;    
          res = mySQLstmt.executeQuery(query1);
      } catch (SQLException ex) {
          Logger.getLogger(UpgradeEEPOrderDB.class.getName()).log(Level.SEVERE, null, ex);
          System.out.println("Problems checking field userID in table orders..");            
      } // end try-catch
      
      try {
          //if previous select returned null then we need to create the field
          if ( !res.first() )
          {
               String query1 = "ALTER TABLE orders ADD userid CHAR(20) ";  
               mySQLstmt.execute(query1);
               System.out.println("Added field userID to table orders..");
          } else {
               System.out.println("field userID already exists in table orders...no action");
          }
           
      } catch (SQLException ex) {
          Logger.getLogger(UpgradeEEPOrderDB.class.getName()).log(Level.SEVERE, null, ex);
          System.out.println("Problems adding field userID to table orders..");            
      } // end try-catch
      
      
      //add time created field to table to track when an order was created
      try {
          //check first if field timecreated already exists
          String query1 = "SELECT * FROM information_schema.COLUMNS "
              + "WHERE TABLE_SCHEMA = 'orderinfo' AND TABLE_NAME = 'orders' "
              + "AND COLUMN_NAME = 'timecreated'";
          res = null;    
          res = mySQLstmt.executeQuery(query1);
      } catch (SQLException ex) {
          Logger.getLogger(UpgradeEEPOrderDB.class.getName()).log(Level.SEVERE, null, ex);
          System.out.println("Problems checking field timecreated in table orders..");            
      } // end try-catch
      
      try {
          //if previous select returned null then we need to create the field
          if ( !res.first() )
          {
                String query1 = "ALTER TABLE orders ADD timecreated BIGINT ";
                mySQLstmt.execute(query1);
                System.out.println("Added field timecreated to table orders..");
          } else {
                System.out.println("field timecreated already exists in table orders...no action");
          }
                        
        } catch (SQLException ex) {
            Logger.getLogger(UpgradeEEPOrderDB.class.getName()).log(Level.SEVERE, null, ex);
            System.out.println("Problem adding field timecreated to table orders..");
        } // end try-catch
      
     
      //add time shipped field to table to track when an order was shipped
      try {
          //check first if field timeshipped already exists
          String query1 = "SELECT * FROM information_schema.COLUMNS "
              + "WHERE TABLE_SCHEMA = 'orderinfo' AND TABLE_NAME = 'orders' "
              + "AND COLUMN_NAME = 'timeshipped'";
          res = null;    
          res = mySQLstmt.executeQuery(query1);
      } catch (SQLException ex) {
          Logger.getLogger(UpgradeEEPOrderDB.class.getName()).log(Level.SEVERE, null, ex);
          System.out.println("Problems checking field timeshipped in table orders..");            
      } // end try-catch
      
      try {
          if ( !res.first() )
          {
                String query1 = "ALTER TABLE orders ADD timeshipped BIGINT ";
                mySQLstmt.execute(query1);
                System.out.println("Added field timeshipped to table orders..");
          } else {
                System.out.println("field timeshipped already exists in table orders...no action");
          }      
      } catch (SQLException ex) {
          Logger.getLogger(UpgradeEEPOrderDB.class.getName()).log(Level.SEVERE, null, ex);
          System.out.println("Problems adding field timeshipped to table orders..");
      }

      
      // create table app_users to hold user information and add 3 users
      try {
   
           String query1 = "DROP TABLE IF EXISTS app_users ";
           mySQLstmt.execute(query1);
           System.out.println("Dropped app_users table...");
           query1 = "CREATE TABLE app_users ( " +
                " `userid` varchar(20) DEFAULT NULL, `username` varchar(80) DEFAULT NULL, " +
                " `passwd` varchar(10) DEFAULT NULL, `created_dt` DATETIME DEFAULT NULL, " +
                " `modified_dt` DATETIME DEFAULT NULL, `last_login_time` DATETIME DEFAULT NULL, " + 
                " `last_logout_time` DATETIME DEFAULT NULL, PRIMARY KEY (`userid`))";
           mySQLstmt.execute(query1);
           System.out.println("Created app_users table...");
           
           query1 = "INSERT INTO `app_users` VALUES ( 'lrocha', 'Luis Rocha'," +
                    " 'tst_passwd', NULL, NULL, NULL, NULL)";
           mySQLstmt.execute(query1);
           System.out.println("Added lrocha user...");
           query1 = "INSERT INTO `app_users` VALUES ('kray', 'Krystal Ray', " +
                    " 'tst_passwd', NULL, NULL, NULL, NULL)";
           mySQLstmt.execute(query1);
           System.out.println("Added kray user...");
           query1 = "INSERT INTO `app_users` VALUES ('subbu', 'Subbu Narashiman'," +
                    " 'tst_passwd', NULL, NULL, NULL, NULL)";
           mySQLstmt.execute(query1);
           System.out.println("Added subbu user...");
           
               
      } catch (SQLException ex) {
          Logger.getLogger(UpgradeEEPOrderDB.class.getName()).log(Level.SEVERE, null, ex);
          System.out.println("Problems adding table app_users ...");
      }
      
      //create table user_roles to hold user role information and add 3 users
      try {
   
           String query1 = "DROP TABLE IF EXISTS user_roles ";
           mySQLstmt.execute(query1);
           System.out.println("Dropped user_roles table...");
           query1 = "CREATE TABLE user_roles ( " +
                " `userid` varchar(20) DEFAULT NULL, `rolename` varchar(20) DEFAULT NULL, " +
                " `created_dt` DATETIME DEFAULT NULL, " +
                " `modified_dt` DATETIME DEFAULT NULL, PRIMARY KEY (`userid`, `rolename` ))";
           mySQLstmt.execute(query1);
           System.out.println("Created app_users table...");
           
           query1 = "INSERT INTO `user_roles` VALUES ('lrocha', 'User',NULL,NULL )";
           mySQLstmt.execute(query1);
           System.out.println("Added lrocha to user role...");
           
           query1 = "INSERT INTO `user_roles` VALUES ('kray', 'User',NULL,NULL )";
           mySQLstmt.execute(query1);
           System.out.println("Added kray to user role...");           
           
           query1 = "INSERT INTO `user_roles` VALUES ('subbu', 'IT Manager',NULL,NULL )";
           mySQLstmt.execute(query1);
           System.out.println("Added subbu to IT Manager role...");
           
      } catch (SQLException ex) {
          Logger.getLogger(UpgradeEEPOrderDB.class.getName()).log(Level.SEVERE, null, ex);
          System.out.println("Problems adding table uuser_roles ...");
      }

      
      //create table user_log to hold user log information
      try {
   
           String query1 = "DROP TABLE IF EXISTS user_log ";
           mySQLstmt.execute(query1);
           System.out.println("Dropped user_log table...");
           query1 = "CREATE TABLE user_log ( " +
                " `userid` varchar(20) DEFAULT NULL, `database` varchar(20) DEFAULT NULL, " +
                " `useraction` varchar(20) DEFAULT NULL, " +
                " `modified_dt` DATETIME DEFAULT NULL, PRIMARY KEY (`userid`, `modified_dt` ))";
           mySQLstmt.execute(query1);
           System.out.println("Created user_log table...");
                   
      } catch (SQLException ex) {
          Logger.getLogger(UpgradeEEPOrderDB.class.getName()).log(Level.SEVERE, null, ex);
          System.out.println("Problems adding table user_log ...");
      }
      
      
    } // end Upgrade
 
} // end class
